<?php

namespace app\admin\controller;


use app\common\model\ExpertModel;
use app\common\model\ExpertPlanModel;
use app\common\model\ExpertPlayModel;
use app\common\model\LiveCategoryModel;
use app\common\model\MatchesScheduleModel;
use think\Db;
use think\facade\Request;
use think\Validate;

class ExpertController extends BaseController
{

    /**
     * 列表
     * @return mixed
     */
    public function index()
    {
        return $this->fetch();
    }


    public function edit()
    {
        $id          = Request::param('id');
        $expert_info = ExpertModel::get($id);

        $this->assign('expert_info', $expert_info);
        return $this->fetch();
    }

    public function getlist()
    {
        $page         = Request::param("page");
        $limit        = Request::param("limit");
        $grade        = Request::param('grade');
        $nick_name    = Request::param('nick_name');
        $is_logicians = Request::param('is_logicians');
        $start_time   = Request::param('start_time');
        $end_time     = Request::param('end_time');
        $all          = $this->getAllParams();

        $where = [];
        if (is_numeric($grade)) {
            $where[] = ['grade', '=', $grade];
        }
        if (is_numeric($is_logicians)) {
            $where[] = ['is_logicians', '=', $is_logicians];
        }
        if ($nick_name) {
            $where[] = ['nick_name', 'like', "%$nick_name%"];
        }
        if (strtotime($start_time)) {
            $where[] = ['create_time', '>=', strtotime($start_time)];
        }
        if (strtotime($end_time)) {
            $where[] = ['create_time', '<=', strtotime($end_time)];
        }

        $order = "id desc";
        if (isset($all["field"]) && $all["field"] && isset($all["order"]) && $all["order"]) {
            $order = sprintf("%s %s", $all["field"], $all["order"]);
        }

        $list = ExpertModel::with(['admin'])->where($where)
            ->limit(($page - 1) * $limit, $limit)
            ->order($order)
            ->select();

        if (count($list) > 0) {
            $count = ExpertModel::where($where)->count();


            foreach ($list as &$item) {
                $item->create_time = date('Y-m-d H:i:s', $item->create_time);
                // 命中率
                $item->win_rate        = $item->win_rate ?? 0;
                $item->attention_count = $item->attention_count > 0 ? $item->attention_count : 0;
                $item->fans_count      = $item->fans_count > 0 ? $item->fans_count : 0;
                $item->flat_times      = $item->flat_times > 0 ? $item->flat_times : 0;
                $item->profit_rate     = $item->profit_rate ?? 0;
            }

            unset($expert_ids, $expert_done, $expert_statis);
            return json(["code" => 0, "msg" => "", "data" => $list, "count" => $count]);
        }
        return self::bulidFail("未查询到相关数据");
    }


    /**
     * 编辑post
     * @return \think\response\Json
     */
    public function edit_post()
    {
        $id        = Request::param('id');
        $all_param = Request::param();

        $validate = new Validate([
            'nick_name'    => 'require|length:2,8|chsDash',
            'profile'      => 'require|url',
            'resume'       => 'length:2,20',
            'grade'        => 'require|in:1,2,3',
            'is_logicians' => 'require|in:0,1',
        ], [
            "nick_name.require" => "昵称不能为空",
            "nick_name.length"  => "昵称在2-8个字符",
            "nick_name.chsDash" => "昵称中文、英文、_和-组成",
            "profile"           => "头像路径错误",
            "resume.length"     => "简介在2-20个字符之间",
            "grade"             => "请正确选择专家等级",
            "is_logicians.in"   => "请正确选择热门专家",
        ]);

        if (!$validate->check($all_param)) {
            return self::bulidFail($validate->getError());
        }

        $expert_info = ExpertModel::get($id);
        if (empty($expert_info)) {
            return self::bulidFail("专家信息不存在");
        }

        //检测昵称是否存在
        $c_where    = [
            ['id', '<>', $expert_info->id],
            ['nick_name', '=', $all_param['nick_name']],
        ];
        $check_name = ExpertModel::where($c_where)->find();
        if (!empty($check_name)) {
            return self::bulidFail('昵称已经存在,请更换一个');
        }


        $expert_info->admin_id = $this->admin_id;

        if ($expert_info->save($all_param)) {

            $this->writeExpertStatis($expert_info);

            // 记录日志
            $log_content = "修改专家信息，ID：{$expert_info->id}，昵称：{$expert_info->nick_name}";
            parent::addAdminLog($log_content, $expert_info->user_id);

            return self::bulidSuccess();
        }
        return self::bulidFail();
    }

    /**
     * 专家统计导出excel
     * @throws \think\exception\DbException
     */
    public function excel()
    {
        set_time_limit(0);
        $grade        = Request::param('grade');
        $nick_name    = Request::param('nick_name');
        $is_logicians = Request::param('is_logicians');
        $start_time   = Request::param('start_time');
        $end_time     = Request::param('end_time');

        $where = [];
        if ($grade) {
            $where[] = ['grade', '=', $grade];
        }
        if ($grade) {
            $where[] = ['is_logicians', '=', $is_logicians];
        }
        if ($nick_name) {
            $where[] = ['nick_name', 'like', "%$nick_name%"];
        }
        if (strtotime($start_time)) {
            $where[] = ['create_time', '>=', strtotime($start_time)];
        }
        if (strtotime($end_time)) {
            $where[] = ['create_time', '<=', strtotime($end_time)];
        }

        $expert_model = ExpertModel::with(['admin'])->where($where);

        $list      = [];
        $grad_type = [
            1 => '初级专家',
            2 => '中级专家',
            3 => '高级专家',
        ];
        $expert_model->chunk(500, function ($querys) use (&$list, $grad_type) {


            foreach ($querys as $query) {
                // 胜率
                $query->win_rate = $query->win_rate ?? 0;
                $win_rate        = $query->plan_done_count . '中' . $query->win_rate;
                if ($query->plan_done_count >= 10) {
                    $win_rate = '10中' . $query->win_rate;
                }

                $list[] = [
                    $query->id,
                    $query->user_id,
                    $query->nick_name,
                    $query->resume,
                    isset($grad_type[$query->grade]) ? $grad_type[$query->grade] : '',
                    $query->is_logicians == 1 ? '是' : '否',
                    $query->attention_count > 0 ? $query->attention_count : 0,
                    $query->fans_count > 0 ? $query->fans_count : 0,
                    $query->plan_count,
                    $query->plan_done_count,
                    $query->flat_times > 0 ? $query->flat_times : 0,
                    $query->plan_done_count,
                    $query->lost_times,
                    $win_rate,
                    $query->profit_rate ?? 0,
                    $query->serial_win . '连红',
                    $query->admin ? $query->admin->name : '',
                    $query->create_time > 0 ? date('Y-m-d H:i:s', $query->create_time) : ''
                ];
            }
        }, 'id', 'desc');

        $file_title = [
            'ID', '用户ID', '专家昵称', '简介', '专家等级', '热门专家',
            '关注人数', '粉丝数', '累计发布', '已开奖', '赢', '走', '输',
            '胜率', '回报率', '连红', '创建人', '创建日期'
        ];
        $file_name  = '专家管理' . date('Y年m月d日H点');

        // 记录日志
        parent::addAdminLog('导出专家管理报表');

        return export_excel($list, $file_title, $file_name, '专家管理');
    }

    /**
     * 方案列表
     * @return mixed
     */
    public function plan()
    {
        return $this->fetch();
    }

    /**
     * 发布方案
     * @return mixed
     */
    public function plan_add()
    {
        $categorys   = LiveCategoryModel::where("status", 1)->order('sort', 'asc')->order('id', 'desc')->select();
        $expert_list = ExpertModel::field('id,nick_name')->select();

        $this->assign('categorys', $categorys);
        $this->assign('expert_list', $expert_list);
        return $this->fetch();
    }

    /**
     * 编辑方案
     * @return mixed
     */
    public function plan_edit()
    {
        $id          = Request::param('id');
        $expert_plan = ExpertPlanModel::with(['expertPlay'])->get($id);

        // 胜/主/大，平/盘口/盘口，负/客/小
        $expert_plan->main_one_odds        = '';
        $expert_plan->main_two_odds        = '';
        $expert_plan->main_three_odds      = '';
        $expert_plan->main_one_recommend   = 0;
        $expert_plan->main_two_recommend   = 0;
        $expert_plan->main_three_recommend = 0;
        $expert_plan->fee_pay =  !empty($expert_plan->fee_pay) ? bcdiv($expert_plan->fee_pay, '100', 2) : 0;

        if ($expert_plan->expert_play) {
            foreach ($expert_plan->expert_play as $val) {
                switch ($val->type) {
                    case 1:
                        $expert_plan->main_one_odds      = $val->odds;
                        $expert_plan->main_one_recommend = $val->recommend;
                        break;
                    case 2:
                        $expert_plan->main_two_odds      = $val->odds;
                        $expert_plan->main_two_recommend = $val->recommend;

                        break;
                    case 3:
                        $expert_plan->main_three_odds      = $val->odds;
                        $expert_plan->main_three_recommend = $val->recommend;

                        break;
                }
            }
        }

        // 专家列表
        $expert_list = ExpertModel::field('id,nick_name')->select();

        $this->assign('expert_list', $expert_list);
        $this->assign('expert_plan', $expert_plan);
        return $this->fetch();
    }

    /**
     * 方案列表
     * @return \think\response\Json
     */
    public function get_plan_list()
    {
        $page     = Request::param("page");
        $limit    = Request::param("limit");
        $id       = Request::param('id');
        $title    = Request::param('title');
        $is_win   = Request::param('is_win');
        $sport_id = Request::param('sport_id');

        $all = $this->getAllParams();

        $where = [];
        if ($id) {
            $where[] = ['id', '=', $id];
        }
        if ($title) {
            $where[] = ['title', 'like', "%$title%"];
        }
        if (is_numeric($is_win)) {
            $where[] = ['is_win', '=', $is_win];
        }
        if (is_numeric($sport_id)) {
            $where[] = ['sport_id', '=', $sport_id];
        }
        // 专家昵称搜索
        if (isset($all["nick_name"]) && mb_strlen($all["nick_name"], 'UTF-8') > 0) {
            $e_where    = [
                ['nick_name', 'like', "%{$all["nick_name"]}%"]
            ];
            $expert_ids = ExpertModel::where($e_where)->column('id');
            $where[]    = ['expert_id', 'in', $expert_ids];
        }

        $order = "id desc";
        if (isset($all["field"]) && $all["field"] && isset($all["order"]) && $all["order"]) {
            $order = sprintf("%s %s", $all["field"], $all["order"]);
        }

        $list = ExpertPlanModel::with(['matchSchedule', 'expert', 'expertPlay'])->where($where)
            ->limit(($page - 1) * $limit, $limit)
            ->order($order)
            ->select();

        if (count($list) > 0) {
            $count = ExpertPlanModel::where($where)->count();

            foreach ($list as &$item) {
                $item->create_time = date('Y-m-d H:i:s', $item->create_time);
                $item->awards_time = $item->awards_time > 0 ? date('Y-m-d H:i:s', $item->awards_time) : '';
                $item->fee_pay =  !empty($item->fee_pay) ? bcdiv($item->fee_pay, '100', 2) : 0;

                // 预测赢家
                $item->recommend_str = '';
                foreach ($item->expert_play as $val) {
                    if ($val->recommend == 1) {
                        $item->recommend_str = $val->play_name;
                    }
                }

                $item->expert_play_name_str = '';
                if ($item->expert_play) {
                    $expert_play_arr            = $item->expert_play->toarray();
                    $expert_play_arr            = array_column($expert_play_arr, 'play_name');
                    $item->expert_play_name_str = implode('，', $expert_play_arr);
                }
                unset($item->expert_play);
            }
            return json(["code" => 0, "msg" => "", "data" => $list, "count" => $count]);
        }
        return self::bulidFail("未查询到相关数据");
    }

    public function plan_add_post()
    {
        $all_param = Request::param();

        $validate = new Validate([
            'title'     => 'require|length:10,35',
            'expert_id' => 'require|number',
            'sport_id'  => 'require|in:1,2',
            'match_id'  => 'require|number',
            'play_type' => 'require|in:1,2,3',
            'fee_type'  => 'require|in:0,1,2',
            'fee_pay'   => 'float',
            'content'   => 'require|length:2,1000',
        ], [
            "title"     => "标题在10-35个字符",
            "expert_id" => "请选择专家",
            "sport_id"  => "请选择联赛分类",
            "match_id"  => "请选择赛事",
            "play_type" => "请选择玩法类型",
            "fee_type"  => "请选择价格单位",
            "fee_pay"   => "价格单位元,保留两位小数",
            'content'   => '分析内容在2-1000个字符'
        ]);

        if (!$validate->check($all_param)) {
            return self::bulidFail($validate->getError());
        }

        !empty($all_param['fee_pay']) && ($all_param['fee_pay'] = rtrim(trim($all_param['fee_pay']), '.'));
        if ($all_param['fee_type'] > 0 && !empty($all_param['fee_pay']) && is_numeric($all_param['fee_pay']) && bccomp($all_param['fee_pay'], '0', 2)) {
            $all_param['fee_pay'] = bcmul($all_param['fee_pay'], '100', 2);
        } else {
            $all_param['fee_type'] = 0;
        }

        // 专家信息
        $expert_model = ExpertModel::get($all_param['expert_id']);
        if (!$expert_model) {
            return self::bulidFail("未查询到专家信息");
        }


        // 赛事信息查询
        $field      = 'id,match_id,comp,comp_short_zh,sport_id';
        $match_info = MatchesScheduleModel::where('match_id', $all_param['match_id'])->field($field)->find();
        if (empty($match_info)) {
            return self::bulidFail("赛事信息错误");
        }

        $expert_plan = new ExpertPlanModel($all_param);

        $expert_plan->create_time   = time();
        $expert_plan->comp          = $match_info->comp;
        $expert_plan->comp_short_zh = $match_info->comp_short_zh;
        $expert_plan->sport_id      = $match_info->sport_id;

        // 赔率处理
        $play_type_arr = $all_param['expert_play'];

        // 专家预测处理
        $expert_play_win = $all_param['expert_play_win'];

        // 玩法各个子名称
        $baskball_play_name_arr = [
            1 => '主',
            3 => '客',
        ];

        $play_name_arr = [
            // 欧指
            1 => [
                1 => '胜',
                2 => '平',
                3 => '负',
            ],
            // 让球
            2 => [
                1 => '主',
                2 => '',
                3 => '客',
            ],
            // 大小
            3 => [
                1 => '大',
                2 => '',
                3 => '小',
            ]
        ];


        Db::startTrans();
        try {
            if (!$expert_plan->save()) {
                throw new \Exception('添加失败');
            }

            $play_data = [];
            foreach ($play_type_arr as $key => $val) {
                $play_name = !empty($play_name_arr[$expert_plan->play_type][$key]) ? $play_name_arr[$expert_plan->play_type][$key] : '';
                if ($expert_plan->sport_id == 2) {
                    $play_name = !empty($baskball_play_name_arr[$key]) ? $baskball_play_name_arr[$key] : '';
                }

                $odds_preg = '/^(\-)?+[0-9]+(.[0-9]{2})$/';
                switch ($key) {
                    case 1:
                        if (!preg_match($odds_preg, $val)) {
                            throw new \Exception($play_name . '赔率需要填入两位小数');
                        }
                        break;
                    case 2:
                        if ($expert_plan->play_type != 1) {
                            // 大小/让球中间的赔率  格式  0.11、-1.2、122、 -1/3
                            if (!preg_match('/^((\-)?\d+(\.\d{1,2})?(\/(\-)?\d+(\.\d{1,2})?)?)$/', $val)) {
                                throw new \Exception('盘口赔率只支持输入两位小数、整数、分号、正负数');
                            }
                        } else if (!preg_match($odds_preg, $val)) {
                            throw new \Exception($play_name . '赔率需要填入两位小数');
                        }

                        break;
                    case 3:
                        if (!preg_match($odds_preg, $val)) {
                            throw new \Exception($play_name . '赔率需要填入两位小数');
                        }
                        break;
                }

                $play_data[] = [
                    'play_name'      => $play_name . $val,
                    'expert_plan_id' => $expert_plan->id,
                    'recommend'      => $expert_play_win == $key ? 1 : 0,
                    'type'           => $key,
                    'odds'           => $val,
                ];
            }

            $expert_play_obj = new ExpertPlayModel();
            if (!$expert_play_obj->saveAll($play_data)) {
                throw new \Exception('添加失败');
            }

            // 统计信息
            $this->writeExpertStatis($expert_model);

            // 记录日志
            $log_content = "创建有料发帖信息，ID：{$expert_plan->id}，标题：{$expert_plan->title}";
            parent::addAdminLog($log_content);

            Db::commit();
            return self::bulidSuccess();
        } catch (\Exception $e) {
            Db::rollback();
            return self::bulidFail($e->getMessage());
        }
    }


    public function plan_edit_post()
    {
        $all_param = Request::param();
        $id        = Request::param('id');

        $validate = new Validate([
            'title'     => 'require|length:10,35',
            'expert_id' => 'require|number',
            'sport_id'  => 'require|in:1,2',
            'match_id'  => 'require|number',
            'play_type' => 'require|in:1,2,3',
            'fee_type'  => 'require|in:0,1,2',
            'fee_pay'   => 'float',
            'content'   => 'require|length:2,1000',
        ], [
            "title"     => "标题在10-35个字符",
            "expert_id" => "请选择专家",
            "sport_id"  => "请选择联赛分类",
            "match_id"  => "请选择赛事",
            "play_type" => "请选择玩法类型",
            "fee_type"  => "请选择价格单位",
            "fee_pay"   => "价格单位必须为整数",
            'content'   => '分析内容在2-1000个字符'
        ]);

        if (!$validate->check($all_param)) {
            return self::bulidFail($validate->getError());
        }

        !empty($all_param['fee_pay']) && ($all_param['fee_pay'] = rtrim(trim($all_param['fee_pay']), '.'));
        if ($all_param['fee_type'] > 0) {
            if (isset($all_param['fee_pay']) && is_numeric($all_param['fee_pay'])) {
                switch (bccomp($all_param['fee_pay'], '0', 2)) {
                    case 0:
                        $all_param['fee_pay'] = 0;
                        break;
                    case 1:
                        $all_param['fee_pay'] = bcmul($all_param['fee_pay'], '100', 2);
                        break;
                    default:
                        unset($all_param['fee_pay']);
                }
            } else {
                unset($all_param['fee_pay']);
            }
        } elseif ($all_param['fee_type'] == 0) {
            $all_param['fee_pay'] = 0;
        }

        // 专家信息
        $expert_model = ExpertModel::get($all_param['expert_id']);
        if (!$expert_model) {
            return self::bulidFail("未查询到专家信息");
        }

        $expert_plan = ExpertPlanModel::with('expertPlay')->get($id);
        if (empty($expert_plan)) {
            return self::bulidFail('有料帖子信息不存在');
        }

        // 查询赛事信息
        $field      = 'id,match_id,comp,comp_short_zh,sport_id';
        $match_info = MatchesScheduleModel::where('match_id', $all_param['match_id'])->field($field)->find();
        if (empty($match_info)) {
            return self::bulidFail("赛事信息错误");
        }

        $all_param['match_id']      = $match_info->match_id;
        $all_param['comp']          = $match_info->comp;
        $all_param['comp_short_zh'] = $match_info->comp_short_zh;
        $all_param['sport_id']      = $match_info->sport_id;

        // 赔率处理
        $play_type_arr = $all_param['expert_play'];

        // 专家预测处理
        $expert_play_win = $all_param['expert_play_win'];

        // 玩法各个子名称
        // 篮球：
        //   欧指：主、客
        //   让球：主、盘口、客
        //   大小：主、盘口、客
        // 足球：
        //   欧指：胜、平、负
        //   让分：主、盘口、客
        //   总分：大、盘口、小
        $baskball_play_name_arr = [
            1 => '主',
            3 => '客',
        ];

        // 足球玩法名字
        $play_name_arr = [
            // 欧指
            1 => [
                1 => '胜',
                2 => '平',
                3 => '负',
            ],
            // 让球
            2 => [
                1 => '主',
                2 => '',
                3 => '客',
            ],
            // 大小
            3 => [
                1 => '大',
                2 => '',
                3 => '小',
            ]
        ];

        // 赔率三种玩法对应的表id数组
        $expert_play = $expert_plan->expert_play->toArray();
        $expert_play = array_column($expert_play, 'id', 'type');

        Db::startTrans();
        try {
            if (!$expert_plan->save($all_param)) {
                throw new \Exception('编辑失败');
            }

            foreach ($play_type_arr as $key => $val) {
                $play_name = !empty($play_name_arr[$expert_plan->play_type][$key]) ? $play_name_arr[$expert_plan->play_type][$key] : '';

                if ($expert_plan->sport_id == 2) {
                    $play_name = !empty($baskball_play_name_arr[$key]) ? $baskball_play_name_arr[$key] : '';
                }

                $odds_preg = '/^(\-)?+[0-9]+(.[0-9]{2})$/';
                switch ($key) {
                    case 1:
                        if (!preg_match($odds_preg, $val)) {
                            throw new \Exception($play_name . '赔率需要填入两位小数');
                        }
                        break;
                    case 2:
                        if ($expert_plan->play_type != 1) {
                            // 大小/让球中间的赔率  格式  0.11、-1.2、122、 -1/3
                            if (!preg_match('/^((\-)?\d+(\.\d{1,2})?(\/(\-)?\d+(\.\d{1,2})?)?)$/', $val)) {
                                throw new \Exception('盘口赔率只支持输入两位小数、整数、分号、正负数');
                            }
                        } else if (!preg_match($odds_preg, $val)) {
                            throw new \Exception($play_name . '赔率需要填入两位小数');
                        }

                        break;
                    case 3:
                        if (!preg_match($odds_preg, $val)) {
                            throw new \Exception($play_name . '赔率需要填入两位小数');
                        }
                        break;
                }

                $play_data[$key] = [
                    'play_name'      => $play_name . $val,
                    'expert_plan_id' => $expert_plan->id,
                    'recommend'      => $expert_play_win == $key ? 1 : 0,
                    'type'           => $key,
                    'odds'           => $val,
                ];
                if (isset($expert_play[$key]) && $expert_play[$key] > 0) {
                    $play_data[$key]['id'] = $expert_play[$key];
                }
            }

            // 删除不存在的玩法，这地方涉及到有两个和三个的，从三个改成两个，要删除掉
            $play_ids   = array_column($play_data, 'id');
            $pdel_where = [
                ['expert_plan_id', '=', $expert_plan->id],
                ['id', 'not in', $play_ids],
            ];

            ExpertPlayModel::where($pdel_where)->delete();


            $expert_play_obj = new ExpertPlayModel();
            if (!$expert_play_obj->saveAll($play_data)) {
                throw new \Exception('编辑失败');
            }

            // 统计信息
            $this->writeExpertStatis($expert_model);

            // 记录日志
            $log_content = "编辑有料发帖信息，ID：{$expert_plan->id}，标题：{$expert_plan->title}";
            parent::addAdminLog($log_content);

            Db::commit();
            return self::bulidSuccess('编辑成功');
        } catch (\Exception $e) {
            Db::rollback();
            return self::bulidFail($e->getMessage());
        }
    }


    /**
     * 编辑方案
     * @return mixed
     */
    public function plan_set_win()
    {
        $id          = Request::param('id');
        $expert_plan = ExpertPlanModel::get($id);
        if (!$expert_plan) {
            return self::bulidFail('有料帖子信息不存在');
        }

        if ($expert_plan->is_win != 0) {
            return self::bulidFail('已经设置过开奖');
        }
        $this->assign('expert_plan', $expert_plan);
        return $this->fetch();
    }

    /**
     * 发帖信息开奖
     */
    public function plan_set_win_post()
    {
        $id     = Request::param('id', 0, 'intval');
        $is_win = Request::param('is_win', 0, 'intval');
        if ($id < 1) {
            return self::bulidFail('参数错误');
        }

        $expert_plan = ExpertPlanModel::get($id);
        if (!$expert_plan) {
            return self::bulidFail('有料帖子信息不存在');
        }

        $expert_id = $expert_plan->expert_id;
        // 专家信息
        $expert_model = ExpertModel::get($expert_id);
        if (!$expert_model) {
            return self::bulidFail("未查询到专家信息");
        }

        if ($expert_plan->is_win != 0) {
            return self::bulidFail('已经设置过开奖');
        }

        $expert_plan->is_win      = $is_win;
        $expert_plan->awards_time = time();

        Db::startTrans();
        try {
            if (!$expert_plan->save()) {
                throw new \Exception('设置失败');
            }


            $sql = <<<HTML
update db_expert g1,
    (
    SELECT
    {$expert_id} expert_id,f1.win_times,f1.lost_times,f1.flat_times,f1.win_times + f1.lost_times + f1.flat_times plan_done_count,
    f2.profit_rate,f2.win_rate,
    ifnull( f3.serial_win, 0 ) serial_win
    FROM
    (SELECT count(1) plan_count,ifnull(sum(CASE WHEN is_win = 2 THEN 1 ELSE 0 END),0) win_times,
    ifnull(sum(CASE WHEN is_win = 1 THEN 1 ELSE 0 END),0) lost_times,
    ifnull(sum(CASE WHEN is_win = 3 THEN 1 ELSE 0 END),0) flat_times FROM db_expert_plan WHERE
    expert_id = {$expert_id}) f1
    LEFT JOIN (
    SELECT
    ifnull(round( sum( CASE WHEN a1.is_win = 2 THEN  IF(a1.play_type = 1,abs( a2.odds-1),abs( a2.odds ))  ELSE - 1 END )* 100, 0 ),0) profit_rate,
    ifnull( sum( CASE WHEN a1.is_win = 2 THEN 1 ELSE 0 END ), 0 ) win_rate
    FROM
    ( SELECT z2.id, z2.is_win,z2.play_type FROM db_expert_plan z2,(select awards_time from db_expert_plan where id = {$id})time2 WHERE z2.awards_time<=time2.awards_time AND expert_id = {$expert_id} AND is_win <> 0 ORDER BY z2.awards_time DESC LIMIT 10 ) a1,db_expert_play a2
    WHERE a1.id = a2.expert_plan_id and a2.recommend=1 AND a1.is_win <> 3) f2 ON 1 = 1
    LEFT JOIN (
    SELECT * FROM
    (SELECT d.row_num,@n :=IF(@pre = is_win AND is_win = 2,@n + 1,IF( is_win = 2, 1, 0 )) serial_win,@pre := is_win
    FROM(SELECT id,awards_time,is_win,ROW_NUMBER() OVER(ORDER BY awards_time ) AS row_num from (SELECT z1.id,z1.awards_time,ROW_NUMBER() OVER(ORDER BY awards_time desc) AS row_num,is_win
    FROM db_expert_plan z1,(select awards_time from db_expert_plan where id = {$id}) time1 WHERE z1.awards_time<=time1.awards_time and  expert_id = {$expert_id} AND is_win <> 0 ORDER BY z1.awards_time DESC LIMIT 10) t3 WHERE  is_win <> 3 ) d,
    (SELECT @pre := NULL,@n := 1) r) t4 ORDER BY row_num desc LIMIT 1
    ) f3 ON 1 = 1
    )g2 set g1.win_times=g2.win_times,g1.lost_times=g2.lost_times,g1.flat_times=g2.flat_times,g1.plan_done_count=g2.plan_done_count,g1.profit_rate=g2.profit_rate,g1.win_rate=g2.win_rate,g1.serial_win=g2.serial_win where  g1.id={$expert_id} and g1.id=g2.expert_id
HTML;

            if (!Db::execute($sql)) {
                throw new \Exception('设置失败');
            }


            // 统计信息
            $this->writeExpertStatis($expert_model);

            // 记录日志
            $log_content = "有料发帖信息开奖，ID：{$expert_plan->id},标题：{$expert_plan->title}";
            parent::addAdminLog($log_content);

            Db::commit();
            return self::bulidSuccess('设置成功');
        } catch (\Exception $e) {
            Db::rollback();
            return self::bulidFail('设置失败');
        }
    }


    /**
     * 发帖信息删除
     */
    public function plan_del()
    {
        return self::bulidFail('操作错误');
        $id = Request::param('id', 0, 'intval');
        if ($id < 1) {
            return self::bulidFail('参数错误');
        }

        $expert_plan_info = ExpertPlanModel::get($id);
        if (!$expert_plan_info) {
            return self::bulidFail('有料帖子信息不存在');
        }

        // 专家信息
        $expert_model = ExpertModel::get($expert_plan_info->expert_id);
        if (!$expert_model) {
            return self::bulidFail("未查询到专家信息");
        }

        Db::startTrans();
        try {
            if (!ExpertPlanModel::where(['id' => $id])->delete()) {
                throw new \Exception('编辑失败');
            }

            // 统计信息
            $this->writeExpertStatis($expert_model);

            // 记录日志
            $log_content = "删除有料发帖信息，ID：{$expert_plan_info->id},标题：{$expert_plan_info->title}";
            parent::addAdminLog($log_content);

            Db::commit();
            return self::bulidSuccess('编辑成功');
        } catch (\Exception $e) {
            Db::rollback();
            return self::bulidFail($e->getMessage());
        }

        return self::bulidFail();
    }

    // 联赛信息
    public function get_match()
    {
        $sport_id     = Request::param("sport_id");
        $edit_matchid = Request::param("edit_matchid");
        $where        = [
            ['sport_id', '=', $sport_id],
            ['match_time', '>=', time()],
        ];
        $where_or     = [];
        if ($edit_matchid) {
            $where_or[] = ['match_id', '=', $edit_matchid];
        }

        $field = 'id,match_id,match_time,home,away';
        $list  = MatchesScheduleModel::where($where)->whereOr($where_or)->order('match_time', 'ASC')->field($field)->select();
        if (empty($list)) {
            return self::bulidFail("未查询到相关数据");
        }
        foreach ($list as &$item) {
            $item->match_time_str = date('Y-m-d H:i:s', $item->match_time);
        }

        return json(["code" => 0, "msg" => "", "data" => $list]);
    }


    /**
     * 专家帖子信息统计
     * @param $expert_model
     * @throws \Exception
     */
    protected function writeExpertStatis($expert_model)
    {
        if (!isset($expert_model->id)) {
            throw new \Exception('专家信息不存在');
        }

        $ep_where = [['expert_id', '=', $expert_model->id]];

        // 赢 平 输
        $expert_model->win_times  = ExpertPlanModel::where($ep_where)->where('is_win', 2)->count();
        $expert_model->flat_times = ExpertPlanModel::where($ep_where)->where('is_win', 3)->count();
        $expert_model->lost_times = ExpertPlanModel::where($ep_where)->where('is_win', 1)->count();
        // 有开奖的
        $expert_model->plan_done_count = ExpertPlanModel::where($ep_where)->where([['is_win', '>', 0]])->count();
        $expert_model->plan_count      = ExpertPlanModel::where($ep_where)->count();
        if (!$expert_model->save()) {
            throw new \Exception('专家统计信息不存在');
        }
    }
}
